

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=&#34;auto&#34;>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/ttntt.gitee.io/img/logo.png">
  <link rel="icon" type="image/png" href="/ttntt.gitee.io/img/logo.png">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="一个不甘做程序员的后端程序员">
  <meta name="author" content="nigntbird">
  <meta name="keywords" content="">
  <title>数据库基础 - nigntbird 的个人博客</title>

  <link  rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.staticfile.org/github-markdown-css/4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/ttntt.gitee.io/lib/hint/hint.min.css" />

  
    
    
      
      <link  rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/10.0.0/styles/github-gist.min.css" />
    
  

  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_pf9vaxs7x7b.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_kmeydafke9r.css">


<link  rel="stylesheet" href="/ttntt.gitee.io/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script  src="/ttntt.gitee.io/js/utils.js" ></script>
  <script  src="/ttntt.gitee.io/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 5.2.0"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/ttntt.gitee.io/">&nbsp;<strong>nigntbird 的个人博客</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/ttntt.gitee.io/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/ttntt.gitee.io/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/ttntt.gitee.io/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/ttntt.gitee.io/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/ttntt.gitee.io/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;</a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" href="javascript:">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner intro-2" id="background" parallax=true
         style="background: url('/ttntt.gitee.io/img/bg/post_bg.jpg') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="container page-header text-center fade-in-up">
            <span class="h2" id="subtitle">
              
            </span>

            
              <div class="mt-3">
  
    <span class="post-meta mr-2">
      <i class="iconfont icon-author" aria-hidden="true"></i>
      nigntbird
    </span>
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2020-10-13 09:21" pubdate>
        2020年10月13日 上午
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      4.7k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      阅读了 
      51
       分钟
    </span>
  

  
  
    
      <!-- 不蒜子统计文章PV -->
      <span id="busuanzi_container_page_pv" style="display: none">
        <i class="iconfont icon-eye" aria-hidden="true"></i>
        <span id="busuanzi_value_page_pv"></span> 次
      </span>
    
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid">
  <div class="row">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-md">
      <div class="container nopadding-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto" id="post">
            <!-- SEO header -->
            <h1 style="display: none">数据库基础</h1>
            
              <p class="note note-info">
                
                  本文最后更新于：1 天前
                
              </p>
            
            <div class="markdown-body" id="post-body">
              <h2 id="ACID"><a href="#ACID" class="headerlink" title="ACID"></a>ACID</h2><ul>
<li>原子性（Atomicity）<br>原子性是指事务是一个<strong>不可分割</strong>的工作单位，事务中的操作要么都发生，要么都不发生。</li>
<li>一致性（Consistency）<br>事务前后数据的完整性必须保持一致。</li>
<li>隔离性（Isolation）<br>事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。</li>
<li>持久性（Durability）<br>持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。<h2 id="事务隔离级别"><a href="#事务隔离级别" class="headerlink" title="事务隔离级别"></a>事务隔离级别</h2></li>
</ul>
<p><code>set transaction isolation level #设置事务隔离级别</code><br><code> select @@tx_isolation    #查询当前事务隔离级别</code><br><strong>脏读：</strong> 指一个事务读取了另外一个事务未提交的数据。<br><strong>不可重复读：</strong> <strong><u>在一个事务内</u>**读取表中的某一行数据，</strong><u>多次读取</u><strong>结果不同。（这个不一定是错误，只是某些场合不对）<br>**幻读：</strong> 是指在一个事务内读取到了<strong>别的事务插入</strong>的数据，导致前后读取不一致。（一般是行影响，多了一行）</p>
<table>
<thead>
<tr>
<th align="left">设置</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">Serializable</td>
<td>可避免脏读、不可重复读、虚读情况的发生。（串行化）</td>
</tr>
<tr>
<td align="left">Repeatable read</td>
<td>可避免脏读、不可重复读情况的发生，但存在幻读。（可重复读）</td>
</tr>
<tr>
<td align="left">Read committed</td>
<td>可避免脏读情况发生（读已提交，不可重复读）。</td>
</tr>
<tr>
<td align="left">Read uncommitted</td>
<td>最低级别，以上情况均无法保证。(读未提交)</td>
</tr>
</tbody></table>
<h2 id="MySQL-语句执行过程"><a href="#MySQL-语句执行过程" class="headerlink" title="MySQL 语句执行过程"></a>MySQL 语句执行过程</h2><p>MySQL 主要分为 <strong>Server 层</strong>和<strong>引擎层</strong>，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。</p>
<p><img src="/ttntt.gitee.io/source/img/images/mysql%E7%BB%84%E4%BB%B6.webp" srcset="/ttntt.gitee.io/img/loading.gif"></p>
<h2 id="MySQL语句执行顺序"><a href="#MySQL语句执行顺序" class="headerlink" title="MySQL语句执行顺序"></a>MySQL语句执行顺序</h2><p>(1)from<br>(3) join<br>(2) on<br>(4) where （因为聚集函数还在后面，where中不能使用聚集函数）<br>(5)group by(开始使用select中的别名，后面的语句中都可以使用)<br>(6) avg,sum….<br>(7)having<br>(8) select<br>(9) distinct<br>(10) order by </p>
<h2 id="数据库左右连接"><a href="#数据库左右连接" class="headerlink" title="数据库左右连接"></a>数据库左右连接</h2><p>左连接会把<strong>左表</strong>(左表 left)的内容全部查出来再拼接<strong>右表</strong>(join 右表)，右连接相反的。</p>
<h2 id="Mysql-性能优化"><a href="#Mysql-性能优化" class="headerlink" title="Mysql 性能优化"></a>Mysql 性能优化</h2><p>1、当只要一行数据时使用 limit 1<br>2、选择正确的数据库引擎，<strong>如mysql中：读操作远远多于写操作时，选用MyISIM比较合理</strong><br>3、 用 not exists 代替 not in<br>4、 对操作符的优化，尽量不采用不利于索引的操作符，如使用复合索引时，遵循最左匹配原则。</p>
<p>5、在使用关联查询时，多使用小表驱动大表（因为小表数据量小）。</p>
<p>6、首先建表遵循三范式（讲三范式）</p>
<p>7、使用<strong>join</strong> 操作代替子查询，但join不要超过5个</p>
<h2 id="三范式"><a href="#三范式" class="headerlink" title="三范式"></a>三范式</h2><ul>
<li>1NF：强调的是<strong>列的原子性</strong>，即列不能够再分成其他几列。 </li>
<li>2NF：一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。 </li>
<li>3NF：任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖]。</li>
</ul>
<h2 id="在select语句后面有sum-xx-时-记录会变为一条-很神奇"><a href="#在select语句后面有sum-xx-时-记录会变为一条-很神奇" class="headerlink" title="在select语句后面有sum(xx)时,记录会变为一条(很神奇!)"></a>在select语句后面有sum(xx)时,记录会变为一条(很神奇!)</h2><h1 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h1><h2 id="避免索引失效"><a href="#避免索引失效" class="headerlink" title="避免索引失效"></a>避免索引失效</h2><h3 id="1-注意数据类型的隐式转换"><a href="#1-注意数据类型的隐式转换" class="headerlink" title="1.注意数据类型的隐式转换"></a>1.注意数据类型的隐式转换</h3><p>如<code>select name,phone from customer where id = &#39;111&#39;（id本来是int型）;</code>‘111’发生了隐式转换，id上的索引失效。</p>
<h3 id="2-避免使用双-号的查询条件"><a href="#2-避免使用双-号的查询条件" class="headerlink" title="2.避免使用双%号的查询条件"></a>2.避免使用双%号的查询条件</h3><p>如：<code>a like &#39;%123%&#39;</code>索引失效，（如果无前置%,只有后置%，是可以用到列上的索引的）</p>
<h3 id="3-注意联合索引的最左原则"><a href="#3-注意联合索引的最左原则" class="headerlink" title="3. 注意联合索引的最左原则"></a>3. 注意联合索引的最左原则</h3><p><code>where a&gt;1 and b=2 and c=9</code>有 a,b,c 列的联合索引，在查询条件中有 <strong>a 列的范围查询</strong>，则在 b,c 列上的索引将不会被用到。</p>
<h3 id="4-少使用not-in"><a href="#4-少使用not-in" class="headerlink" title="4.少使用not in"></a>4.少使用not in</h3><p>使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。</p>
<h3 id="5-禁止使用-SELECT-必须使用-SELECT-lt-字段列表-gt-查询"><a href="#5-禁止使用-SELECT-必须使用-SELECT-lt-字段列表-gt-查询" class="headerlink" title="5. 禁止使用 SELECT * 必须使用 SELECT &lt;字段列表&gt; 查询"></a>5. 禁止使用 SELECT * 必须使用 SELECT &lt;字段列表&gt; 查询</h3><p><strong>原因：</strong></p>
<ul>
<li><p>消耗更多的 CPU 和 IO 以网络带宽资源</p>
</li>
<li><p>无法使用覆盖索引</p>
</li>
<li><p>可减少表结构变更带来的影响</p>
</li>
</ul>
<h2 id="MySQL索引类型"><a href="#MySQL索引类型" class="headerlink" title="MySQL索引类型"></a>MySQL索引类型</h2><ul>
<li>==普通索引== ：最基本的索引，它没有任何限制，用于加速查询。<br><code> CREATE INDEX index_mytable_name ON mytable(name);</code></li>
<li>==唯一索引==：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。<br><code> CREATE UNIQUE INDEX index_mytable_name ON mytable(name);</code></li>
<li>==主键索引==：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。<br><code>  CREATE TABLE mytable ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(32) , PRIMARY KEY (`id`) );</code></li>
<li>==组合索引==：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循<strong>最左前缀集合</strong>。<br><code> CREATE INDEX index_mytable_id_name ON mytable(id,name);</code></li>
<li>==全文索引==：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。<br>fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。<br>fulltext索引配合match against操作使用，而不是一般的where语句加like。<br>它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。</li>
<li>*缺点**：虽然索引可以增加查询数据，但对于更新、创建或者删除的时候，需要去维护索引，导致性能会受影响，因此，索引也不能建立太多。</li>
</ul>
<h3 id="主键索引与非主键索引"><a href="#主键索引与非主键索引" class="headerlink" title="主键索引与非主键索引"></a>主键索引与非主键索引</h3><ul>
<li>1.主键索引索引着数据，然而普通索引索引着主键ID值(这是在innodb中，但是如果是myisam中，主键索引和普通索引是没有区别的都是直接索引着数据)</li>
<li>2.当你查询用的是where id=x 时，那只需要扫描一遍主键索引，然后拿到相应数据，但是如果是查询的普通索引的话，那么会先扫描一次普通索引，拿到主键值，然后再去扫主键索引，拿到所需要的数据，这个过程叫做<strong>回表</strong></li>
</ul>
<h2 id="索引存储结构"><a href="#索引存储结构" class="headerlink" title="索引存储结构"></a>索引存储结构</h2><p>常见的MySQL主要有两种结构：<strong>Hash索引和B+ Tree索引</strong>，我们使用的是InnoDB引擎，默认的是B+树。</p>
<h3 id="什么是B-树？"><a href="#什么是B-树？" class="headerlink" title="什么是B+树？"></a>什么是B+树？</h3><p>B+与B-树的区别:</p>
<ul>
<li>(1) 非叶子节点只存储键值信息</li>
<li>(2) 所有叶子节点之间都有链指针<blockquote>
<p>这样做是为了提高区间效率，例如查询key为从18到49的所有数据记录，当找到18后，只要顺着节点和指针顺序遍历就可以以此向访问到所有数据节点，极大提高了区间查询效率。</p>
<ul>
<li>(3) 数据记录都存放在叶子节点中</li>
</ul>
</blockquote>
</li>
</ul>
<p>B-Tree模型：<br><img src="/ttntt.gitee.io/source/img/images/b-Tree.png" srcset="/ttntt.gitee.io/img/loading.gif"></p>
<p>B+Tree模型:<br><img src="/ttntt.gitee.io/source/img/images/B+Tree.png" srcset="/ttntt.gitee.io/img/loading.gif"><br>B+Tree优点:</p>
<ul>
<li>大大减少磁盘I/O读取<br>数据库系统的设计者巧妙利用了<strong>磁盘预读原理</strong>，将一个节点的大小设为等于一个页，这样每个节点需要一次I/O就可以完全载入。</li>
</ul>
<h2 id="聚集索引和非聚集索引"><a href="#聚集索引和非聚集索引" class="headerlink" title="聚集索引和非聚集索引"></a>聚集索引和非聚集索引</h2><p>聚集索引:</p>
<p><strong>数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。</strong>MySQL的InnoDB使用的即为聚簇索引，默认使用主键作为索引，所以必须有主键。</p>
<p><img src="/ttntt.gitee.io/source/img/images/%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95.JPG" srcset="/ttntt.gitee.io/img/loading.gif"></p>
<p>非聚集索引:</p>
<p><strong>该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。</strong>MySQL的MyISAM即为非聚簇索引。</p>
<p>查询<strong>非聚集索引覆盖以外的列</strong>就会变成<strong>聚集索引的全索引扫描（index scan）</strong>查询来避免二次查询，<strong>解决方案：</strong></p>
<blockquote>
<p>建立两列以上的索引（复合索引），即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，这里需要注意最左匹配原则。</p>
</blockquote>
<p><img src="/ttntt.gitee.io/source/img/images/%E8%81%9A%E9%9D%9E%E9%9B%86%E7%B4%A2%E5%BC%95.JPG" srcset="/ttntt.gitee.io/img/loading.gif"></p>
<h1 id="并发"><a href="#并发" class="headerlink" title="并发"></a>并发</h1><h2 id="MVCC-多版本控制"><a href="#MVCC-多版本控制" class="headerlink" title="MVCC (多版本控制)"></a>MVCC (多版本控制)</h2><p>区分下现在有个新前后端分离的叫<code>MVVM</code>.<br>实现了只有<strong>写数据&amp;写数据</strong>之间相互阻塞.只在<code>Read Committed </code>和 <code>Repeatable Read</code>两个隔离级别下工作。<br><a target="_blank" rel="noopener" href="https://blog.csdn.net/Waves___/article/details/105295060">参考文章</a></p>
<p>实现方法: <code>隐藏字段</code>+<code>ReadView</code>+<code>Undo log</code>。</p>
<h3 id="1-三个隐藏字段"><a href="#1-三个隐藏字段" class="headerlink" title="1.三个隐藏字段"></a>1.三个隐藏字段</h3><ul>
<li>DB_TRX_ID(6字节):表示最近一次对本记录行作修改（insert | update）的事务ID</li>
<li>DB_ROLL_PTR(7字节):回滚指针，指向当前记录行的undo log信息</li>
<li>DB_ROW_ID(6字节)：随着新行插入而单调递增的行ID</li>
</ul>
<h3 id="2-ReadView结构"><a href="#2-ReadView结构" class="headerlink" title="2.ReadView结构"></a>2.ReadView结构</h3><p>相当于快照<br>包含四个字段:<br>① low_limit_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。<br>② up_limit_id：活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。<br>③ trx_ids：Read View创建时其他未提交的活跃事务ID列表。<br>④ creator_trx_id：当前创建事务的ID，是一个递增的编号</p>
<h3 id="3-Undo-log"><a href="#3-Undo-log" class="headerlink" title="3.Undo log"></a>3.Undo log</h3><p>Undo log中存储的是老版本数据，当一个事务需要读取记录行时，如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。</p>
<blockquote>
<p>不同事务或者相同事务的对同一记录行的修改，会使该记录行的undo log成为一条链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录</p>
</blockquote>
<p>记录修改流程如下:<br><img src="/ttntt.gitee.io/source/img/images/20200701205716343.png" srcset="/ttntt.gitee.io/img/loading.gif"><br>（1）现在事务A（事务ID为2）对该记录的Honor做出了修改，将Honor改为”fmvp”：</p>
<blockquote>
<p>  ①事务A先对该行加排它锁<br>    ②然后把该行数据拷贝到undo log中，作为旧版本<br>    ③拷贝完毕后，修改该行的Honor为”fmvp”，并且修改DB_TRX_ID为2（事务A的ID）, 回滚指针指向拷贝到undo log的旧版本。（然后还会将修改后的最新数据写入redo log）<br>    ④事务提交，释放排他锁</p>
</blockquote>
<p><img src="/ttntt.gitee.io/source/img/images/20200701210046670.png" srcset="/ttntt.gitee.io/img/loading.gif"></p>
<h2 id="当前读和快照读"><a href="#当前读和快照读" class="headerlink" title="当前读和快照读"></a>当前读和快照读</h2><p>**快照读(snapshot read)**：普通的 select 语句(不会加锁,不包括 select … lock in share mode, select … for update)</p>
<p><strong>当前读(current read) :</strong> select … lock in share mode，select … for update，insert，update，delete 语句（这些语句<strong>会对记录加锁</strong>,获取的是数据库中的<strong>最新数据.</strong></p>
<blockquote>
<p><strong>因为当前读的存在所以MVCC也可能会存在<em>部分幻读</em></strong></p>
</blockquote>
<h2 id="MySQL的锁"><a href="#MySQL的锁" class="headerlink" title="MySQL的锁"></a>MySQL的锁</h2><h3 id="一-表锁"><a href="#一-表锁" class="headerlink" title="一.表锁"></a>一.表锁</h3><ul>
<li>MyISAM存储引擎只支持表锁</li>
<li>分为 读锁/写锁 <code>lock table read/write</code> <strong>读锁不互斥,写锁互斥</strong></li>
</ul>
<h3 id="二-行锁（Record-Lock）"><a href="#二-行锁（Record-Lock）" class="headerlink" title="二.行锁（Record Lock）"></a>二.行锁（Record Lock）</h3><h4 id="锁直接加在索引记录上面，锁住的是key。"><a href="#锁直接加在索引记录上面，锁住的是key。" class="headerlink" title="锁直接加在索引记录上面，锁住的是key。"></a>锁直接加在索引记录上面，锁住的是key。</h4><ul>
<li><p>只有Innodb存储引擎才支持行锁,行锁是针对事务来的</p>
</li>
<li><p>只有在<strong>需要的时候</strong>才会加上,等事务结束后才释放锁,两阶段锁协议</p>
</li>
<li><p>分为共享锁和排他锁</p>
<ul>
<li><p><strong>共享锁:</strong> <code>select xxxx LOCK IN SHARE MODE</code><br>多个事务只能读数据不能改数据.<br>加上共享锁后在事务结束之前其他事务只能再加共享锁.</p>
</li>
<li><p><strong>排他锁</strong>: <code>select xxxx FOR UPDATE</code><br>其他事务不能修改数据也不能再在其上加其他的锁.<em>可以普通查询.</em><br>== MySQL执行增删改语句默认会加上排他锁 ==</p>
</li>
</ul>
<h5 id="产生死锁"><a href="#产生死锁" class="headerlink" title="产生死锁"></a>产生死锁</h5><ul>
<li>原因:肯定得复习下死锁产生的四个条件(互斥，占有且等待，不可抢占，循环等待),翻译过来就是一个事务锁住了资源而长期未提交,其他事务要用资源时,产生死锁.</li>
<li>解决办法<ul>
<li>设置MySQL锁等待超时 <code>innodb_lock_wait_timeout=50 </code>，<em>也可以不要事务autocommit=on</em></li>
<li>设置死锁机制 `innodb_deadlock_detect=on</li>
<li>手动:<code>show processlist;</code> #查看正在执行的sql 然后kill掉</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="三-间隙锁（Gap-Lock）"><a href="#三-间隙锁（Gap-Lock）" class="headerlink" title="三.间隙锁（Gap Lock）"></a>三.间隙锁（Gap Lock）</h3><h5 id="锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。"><a href="#锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。" class="headerlink" title="锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。"></a>锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。</h5><h3 id="四-Next-Key-Lock"><a href="#四-Next-Key-Lock" class="headerlink" title="四.Next-Key Lock"></a>四.Next-Key Lock</h3><h4 id="默认情况下innodb以这种方式加锁-行锁和间隙锁组合起来就叫Next-Key-Lock"><a href="#默认情况下innodb以这种方式加锁-行锁和间隙锁组合起来就叫Next-Key-Lock" class="headerlink" title="默认情况下innodb以这种方式加锁,行锁和间隙锁组合起来就叫Next-Key Lock"></a>默认情况下innodb以这种方式加锁,行锁和间隙锁组合起来就叫Next-Key Lock</h4><ul>
<li>当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。</li>
<li>防止其他事务的插入操作，以此防止幻读的发生。</li>
</ul>
<p><strong>innodb的锁规则:</strong></p>
<ul>
<li>如果查询时没有走索引,会进行全表扫描,所以行锁会升级会表锁.</li>
<li>如果更新条件为索引字段，但是并非唯一索引（包括主键索引）,会使用Next-Key Lock.</li>
<li>如果更新条件为唯一索引，则使用Record Lock（记录锁).</li>
<li>使用<code>in</code>语句加锁时会按从小到大顺序加锁.</li>
</ul>
<h3 id="MySQl日志"><a href="#MySQl日志" class="headerlink" title="MySQl日志"></a>MySQl日志</h3><ul>
<li><p>1：redo log(重做日志)<br>　确保事务的持久性,redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。</p>
</li>
<li><p>2：undo log(回滚日志)</p>
<ul>
<li>保存了事务发生之前的数据的一个版本，可以用于回滚.</li>
<li>提供多版本并发控制下的非锁定读（MVCC）.</li>
</ul>
</li>
<li><p>3：binlog(二进制日志 )</p>
<ul>
<li>用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。</li>
<li>用于数据库的基于时间点的还原。</li>
</ul>
</li>
</ul>
<h2 id="MySQL自增id用完怎么办"><a href="#MySQL自增id用完怎么办" class="headerlink" title="MySQL自增id用完怎么办?"></a>MySQL自增id用完怎么办?</h2><ul>
<li>能避免的时候就使用bigint作为主键id.</li>
<li>(innodb中)<strong>没设置</strong>主键时使用隐藏字段<code>DB_ROW_ID</code></li>
<li>使用thread_id(<code>SHOW PROCESSLIST</code>结果的第一列),每建立一个连接id加1,但是不会重复.</li>
</ul>
<h2 id="区分join操作的驱动表"><a href="#区分join操作的驱动表" class="headerlink" title="区分join操作的驱动表"></a>区分join操作的驱动表</h2><ul>
<li>区分:<code>left join</code>时左表为驱动表<strong>（主表）</strong>,右表为被驱动表.<br>right join时相反,inner join时小表为驱动表</li>
<li><strong>优化方案:</strong><ul>
<li>在sql优化中，永远是以小表驱动大表.</li>
<li>给被驱动表建立索引才会有效.</li>
</ul>
</li>
</ul>
<h2 id="MySQL-从一个表中查数据并插入另一个表实现方法"><a href="#MySQL-从一个表中查数据并插入另一个表实现方法" class="headerlink" title="MySQL 从一个表中查数据并插入另一个表实现方法:"></a>MySQL 从一个表中查数据并插入另一个表实现方法:</h2><ul>
<li>如果两张张表（导出表和目标表）的字段一致，并且希望插入全部数据，可以用这种方法：<br><code>INSERT INTO  目标表  SELECT  * FROM  来源表 ;</code></li>
<li>如果只希望导入指定字段，可以用这种方法(注意两张表字段类型一致)：<br><code>INSERT INTO  目标表 (字段1, 字段2, ...)  SELECT   字段1, 字段2, ...   FROM  来源表 ;</code> </li>
</ul>
<h2 id="大表优化"><a href="#大表优化" class="headerlink" title="大表优化"></a>大表优化</h2><h4 id="1-限定数据的范围"><a href="#1-限定数据的范围" class="headerlink" title="1. 限定数据的范围"></a>1. 限定数据的范围</h4><p>务必禁止不带任何限制数据范围条件的查询语句。</p>
<h4 id="2-读-写分离"><a href="#2-读-写分离" class="headerlink" title="2. 读/写分离"></a>2. 读/写分离</h4><p>经典的数据库拆分方案，主库负责写，从库负责读。</p>
<h4 id="3-垂直分区"><a href="#3-垂直分区" class="headerlink" title="3.垂直分区"></a>3.垂直分区</h4><p><strong>根据数据库里面数据表的相关性进行拆分。</strong> 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。<strong>简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。</strong> </p>
<h4 id="4-水平分区"><a href="#4-水平分区" class="headerlink" title="4. 水平分区"></a>4. 水平分区</h4><p>把一张表的数据拆分为多张表来存。</p>
<p>**保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。==水平拆分最好分库== ** 。</p>
<h2 id="表的列-amp-行-方向的关联"><a href="#表的列-amp-行-方向的关联" class="headerlink" title="表的列 &amp; 行 方向的关联"></a>表的列 &amp; 行 方向的关联</h2><h3 id="行方向"><a href="#行方向" class="headerlink" title="行方向"></a>行方向</h3><p>两个表的行方向的关联，主要是指表与表之间并集、交集等集合运算。</p>
<p><strong>1.表与表之间的合并</strong>。主要使用<code>union</code>这个运算符，其使用方法如下：</p>
<p><code>select 列名 from 表1</code></p>
<p><code>union</code></p>
<p><code>select 列名 from 表2</code></p>
<p><strong>在书写时，需要注意以下几点：</strong></p>
<ul>
<li><p>A.两个select语句中的列数必须相同，而且对应列的数据类型必须一致。</p>
</li>
<li><p>B.order by子句只能放在最后使用。</p>
</li>
<li><p>C.union给出的结果是去除重复行的。也就是说表1和表2没有重复的行，如果想要保留重复行，可以将union换成union all。</p>
</li>
</ul>
<p><strong>2.表和表之间的交集</strong>，可以使用<code>intersect</code>这个运算符。其语法及注意事项与union完全一样：</p>
<p><code>select 列名 from 表1</code></p>
<p><code>intersect</code></p>
<p><code>select 列名 from 表2</code></p>
<p>同样，如果想保留重复行，将intersect换成intersect all即可。结果返回两表之间的公共部分。</p>
<h3 id="列方向"><a href="#列方向" class="headerlink" title="列方向"></a>列方向</h3><ul>
<li><p>inner join——内连接</p>
<p>只会返回两表的共有数据。</p>
</li>
<li><p>outer join——外连接</p>
<ul>
<li><p>左外连接 <code>left join</code></p>
<p> <strong>left 前面</strong>的表作为主表，会查出主表的所有数据。</p>
</li>
<li><p>右外连接</p>
<p><strong>right 后面</strong>的表为主表，会查出主表的所有数据。</p>
</li>
</ul>
</li>
</ul>
<h2 id="什么是池化？"><a href="#什么是池化？" class="headerlink" title="什么是池化？"></a>什么是池化？</h2><p>数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。<strong>在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中</strong>。 连接池还减少了用户必须等待建立与数据库的连接的时间</p>
<h2 id="分库分表后怎么控制全局-id"><a href="#分库分表后怎么控制全局-id" class="headerlink" title="分库分表后怎么控制全局 id"></a>分库分表后怎么控制全局 id</h2><p>生成全局 id 有下面这几种方式：</p>
<ul>
<li><strong>UUID</strong>：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。</li>
<li><strong>数据库自增 id</strong> : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。</li>
<li><strong>利用 redis 生成 id :</strong> 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。</li>
<li><strong>Twitter的snowflake算法</strong> ：Github 地址：<a target="_blank" rel="noopener" href="https://github.com/twitter-archive/snowflake%E3%80%82">https://github.com/twitter-archive/snowflake。</a></li>
</ul>

            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/ttntt.gitee.io/categories/%E5%9F%BA%E7%A1%80/">基础</a>
                    
                      <a class="hover-with-bg" href="/ttntt.gitee.io/categories/%E5%9F%BA%E7%A1%80/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/ttntt.gitee.io/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
                    
                      <a class="hover-with-bg" href="/ttntt.gitee.io/tags/MySQL/">MySQL</a>
                    
                      <a class="hover-with-bg" href="/ttntt.gitee.io/tags/Oracle/">Oracle</a>
                    
                      <a class="hover-with-bg" href="/ttntt.gitee.io/tags/%E9%9D%9E%E5%85%B3%E7%B3%BB%E6%95%B0%E6%8D%AE%E5%BA%93/">非关系数据库</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a target="_blank" href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext row">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/ttntt.gitee.io/2020/10/13/algorithm/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E4%B8%8E%E7%AE%97%E6%B3%95/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">数据结构与算法</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/ttntt.gitee.io/2020/10/12/skill/%E4%BD%BF%E7%94%A8hexo-github%E6%90%AD%E5%BB%BA%E4%B8%AA%E4%BA%BA%E5%8D%9A%E5%AE%A2%E8%BF%87%E7%A8%8B%E4%B8%AD%E7%9A%84%E4%B8%80%E4%BA%9B%E5%9D%91/">
                        <span class="hidden-mobile">使用hexo+github搭建个人博客过程中的一些坑</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
              <!-- Comments -->
              <article class="comments" id="comments">
                
                
  <div id="vcomments"></div>
  <script type="text/javascript">
    function loadValine() {
      addScript('https://cdn.staticfile.org/valine/1.4.14/Valine.min.js', function () {
        new Valine({
          el: "#vcomments",
          app_id: "KlkvDALnR8PRdMQXRDN6iqNu-gzGzoHsz",
          app_key: "xec8mdMRTtI5Hy9fBBjHHoyP",
          placeholder: "说点什么吧",
          path: window.location.pathname,
          avatar: "retro",
          meta: ["nick","mail","link"],
          pageSize: "20",
          lang: "zh-CN",
          highlight: false,
          recordIP: false,
          serverURLs: "",
        });
      });
    }
    waitElementVisible('vcomments', loadValine);
  </script>
  <noscript>Please enable JavaScript to view the <a target="_blank" href="https://valine.js.org" rel="nofollow noopener noopener">comments
      powered by Valine.</a></noscript>


              </article>
            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div id="tocbot"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    
  </main>

  
    <a id="scroll-top-button" href="#" role="button">
      <i class="iconfont icon-arrowup" aria-hidden="true"></i>
    </a>
  

  
    <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
  

  

  

  <footer class="mt-5">
  <div class="text-center py-3">
    <div>
      <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a>
      <i class="iconfont icon-love"></i>
      <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener">
        <span>Fluid</span></a>
    </div>
    
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


    

    
  </div>
</footer>

<!-- SCRIPTS -->
<script  src="https://cdn.staticfile.org/jquery/3.4.1/jquery.min.js" ></script>
<script  src="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/js/bootstrap.min.js" ></script>
<script  src="/ttntt.gitee.io/js/debouncer.js" ></script>
<script  src="/ttntt.gitee.io/js/main.js" ></script>

<!-- Plugins -->


  
    <script  src="/ttntt.gitee.io/js/lazyload.js" ></script>
  



  



  <script defer src="https://cdn.staticfile.org/clipboard.js/2.0.6/clipboard.min.js" ></script>
  <script  src="/ttntt.gitee.io/js/clipboard-use.js" ></script>



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>





  <script  src="https://cdn.staticfile.org/tocbot/4.11.1/tocbot.min.js" ></script>
  <script>
    $(document).ready(function () {
      var boardCtn = $('#board-ctn');
      var boardTop = boardCtn.offset().top;

      tocbot.init({
        tocSelector: '#tocbot',
        contentSelector: '#post-body',
        headingSelector: 'h1,h2,h3,h4,h5,h6',
        linkClass: 'tocbot-link',
        activeLinkClass: 'tocbot-active-link',
        listClass: 'tocbot-list',
        isCollapsedClass: 'tocbot-is-collapsed',
        collapsibleClass: 'tocbot-is-collapsible',
        collapseDepth: 0,
        scrollSmooth: true,
        headingsOffset: -boardTop
      });
      if ($('.toc-list-item').length > 0) {
        $('#toc').css('visibility', 'visible');
      }
    });
  </script>



  <script  src="https://cdn.staticfile.org/typed.js/2.0.11/typed.min.js" ></script>
  <script>
    var typed = new Typed('#subtitle', {
      strings: [
        '  ',
        "数据库基础&nbsp;",
      ],
      cursorChar: "_",
      typeSpeed: 70,
      loop: false,
    });
    typed.stop();
    $(document).ready(function () {
      $(".typed-cursor").addClass("h2");
      typed.start();
    });
  </script>



  <script  src="https://cdn.staticfile.org/anchor-js/4.2.2/anchor.min.js" ></script>
  <script>
    anchors.options = {
      placement: "left",
      visible: "hover",
      
      icon: "❡"
      
    };
    var el = "h1,h2,h3,h4,h5,h6".split(",");
    var res = [];
    for (item of el) {
      res.push(".markdown-body > " + item)
    }
    anchors.add(res.join(", "))
  </script>



  <script  src="/ttntt.gitee.io/js/local-search.js" ></script>
  <script>
    var path = "/ttntt.gitee.io/local-search.xml";
    var inputArea = document.querySelector("#local-search-input");
    inputArea.onclick = function () {
      searchFunc(path, 'local-search-input', 'local-search-result');
      this.onclick = null
    }
  </script>



  <script  src="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.css" />

  <script>
    $('#post img:not(.no-zoom img, img[no-zoom]), img[zoom]').each(
      function () {
        var element = document.createElement('a');
        $(element).attr('data-fancybox', 'images');
        $(element).attr('href', $(this).attr('src'));
        $(this).wrap(element);
      }
    );
  </script>







  
  
    <script>
      !function (e, t, a) {
        function r() {
          for (var e = 0; e < s.length; e++) s[e].alpha <= 0 ? (t.body.removeChild(s[e].el), s.splice(e, 1)) : (s[e].y--, s[e].scale += .004, s[e].alpha -= .013, s[e].el.style.cssText = "left:" + s[e].x + "px;top:" + s[e].y + "px;opacity:" + s[e].alpha + ";transform:scale(" + s[e].scale + "," + s[e].scale + ") rotate(45deg);background:" + s[e].color + ";z-index:99999");
          requestAnimationFrame(r)
        }

        function n() {
          var t = "function" == typeof e.onclick && e.onclick;
          e.onclick = function (e) {
            t && t(), o(e)
          }
        }

        function o(e) {
          var a = t.createElement("div");
          a.className = "heart", s.push({
            el: a,
            x: e.clientX - 5,
            y: e.clientY - 5,
            scale: 1,
            alpha: 1,
            color: c()
          }), t.body.appendChild(a)
        }

        function i(e) {
          var a = t.createElement("style");
          a.type = "text/css";
          try {
            a.appendChild(t.createTextNode(e))
          } catch (t) {
            a.styleSheet.cssText = e
          }
          t.getElementsByTagName("head")[0].appendChild(a)
        }

        function c() {
          return "rgb(" + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + "," + ~~(255 * Math.random()) + ")"
        }

        var s = [];
        e.requestAnimationFrame = e.requestAnimationFrame || e.webkitRequestAnimationFrame || e.mozRequestAnimationFrame || e.oRequestAnimationFrame || e.msRequestAnimationFrame || function (e) {
          setTimeout(e, 1e3 / 60)
        }, i(".heart{width: 10px;height: 10px;position: fixed;background: #f00;transform: rotate(45deg);-webkit-transform: rotate(45deg);-moz-transform: rotate(45deg);}.heart:after,.heart:before{content: '';width: inherit;height: inherit;background: inherit;border-radius: 50%;-webkit-border-radius: 50%;-moz-border-radius: 50%;position: fixed;}.heart:after{top: -5px;}.heart:before{left: -5px;}"), n(), r()
      }(window, document);
    </script>
  











  
    <!-- Baidu Analytics -->
    <script defer>
      var _hmt = _hmt || [];
      (function () {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?68846803d2c2c84a8b0f73b87244ddef";
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
  

  

  

  

  

  





</body>
</html>
